package Dao.Implement;

import Dao.userDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import pojo.playground;
import pojo.room;
import pojo.stadium;
import pojo.user;
import pojo.message;

import java.sql.Date;
import java.util.List;
@Repository(value = "userDaoImplement")
public class userDaoImplement implements userDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public Object loginIn(String studentId) {
        String sql="select password from match_user where student_id=?";
        return jdbcTemplate.queryForObject(sql,String.class,studentId);

    }
    @Override
    public user queryUserByID(Long userID) {
        String sql="select * from match_user where user_id=?";

        return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<user>(user.class),userID);
    }

    @Override
    public user queryUserByStudentId(String studentID) {
        String sql="select * from match_user where student_id=?";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<user>(user.class),studentID);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }


    @Override
    public int register(String studentId, String name, String password) {
        String sql="insert into match_user(name,student_id,password) values(?,?,?)";

        return jdbcTemplate.update(sql,name,studentId,password);
    }

    @Override
    public int sendMessage(Long user_id, Long room_id, String content) {
        String sql="insert into message(user_id,room_id,content) values(?,?,?)";
        return jdbcTemplate.update(sql,user_id,room_id,content);
    }

    @Override
    public List<message> receiveMessage(Long room_id) {
        String sql="select message_id as message_id," +
                "user_id as user_id," +
                "content as content from message where room_id=?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<message>(message.class),room_id);
    }



    @Override
    public List<playground> queryPlaygroundListByStadiumID(Long stadium_id,String sports_id) {
        String sql="select * from playground where stadium_id=? and sport_id=?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<playground>(playground.class),stadium_id,sports_id);
    }


    @Override
    public List<stadium> queryStadiumBySportsId(String sports_id) {
        String querySport="%"+sports_id+"%";
        String sql="select * from stadium where sports_id like ?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<stadium>(stadium.class),querySport);
    }

    @Override
    public List<user> queryUserIDByRoomID(Long room_id) {
        String sql="select * from match_user where user_id in" +
                "(select user_id from room_user where room_id=?)";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<user>(user.class),room_id);
    }

    @Override
    public room queryRoombyRoomID(Long room_id) {
        String sql="select * from room where room_id=? and roomstate=0";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<room>(room.class),room_id);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<room> queryRoom() {
        String sql="select * from room where roomstate=0";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class));
    }

    @Override
    public int createRoom(room room) {
        String sql="insert into room(host_id,capacity,current_number,date,start_time,end_time,money,stadium,playground,sports_id,roomstate,pk_match) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,room.getHost_id(),room.getCapacity(),room.getCurrent_number(),room.getDate(),room.getStart_time(),room.getEnd_time(),room.getMoney(),room.getStadium(),room.getPlayground(),room.getSports_id(),room.getRoomstate(),room.getPk_match());
    }


    @Override
    public int joinRoom(Long room_id, Long user_id,Date date,String category) {
        String sql="insert into room_user values(?,?,0,?,?)";
        return jdbcTemplate.update(sql,user_id,room_id,date,category);
    }

    @Override
    public int updateState(Long room_id, Long user_id, int state) {
       String sql="update room_user set user_state=? where room_id=? and user_id=?";
       return jdbcTemplate.update(sql,state,room_id,user_id);
    }

    @Override
    public int updateRoomState(Long room_id, int state) {
        String sql="update room set roomstate=? where room_id=?";
        return jdbcTemplate.update(sql,state,room_id);
    }

    @Override
    public List<room> queryMatchingRoom(String sports_id) {
        String sql="select * from room where sports_id=? and roomstate=0";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class),sports_id);
    }

    @Override
    public List<room> queryRoomOrderByUserID(Long user_id) {
        String sql="select * from room where room_id in(select room_id from room_user where user_id=?)";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class),user_id);
    }

    @Override
    public List<room> queryRoomOrderByRoomstate(Long user_id,int roomstate) {
        String sql="select * from room where roomstate=? and room_id in(select room_id from room_user where user_id=?)";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class),roomstate,user_id);
    }

    @Override
    public playground queryPlaygroundByName(Long stadiumID, String playgroundName) {
        String sql="select * from playground where play_name=? and stadium_id=?";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<playground>(playground.class),playgroundName,stadiumID);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public stadium queryStadiumByName(String stadiumName) {
        String  sql="select * from stadium where name=?";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<stadium>(stadium.class),stadiumName);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public int deleteRoomById(Long roomId) {
        String sql="delete from room where room_id=?";
        return jdbcTemplate.update(sql,roomId);
    }

    @Override
    public int deleteUserByRoomId(Long roomId) {
        String sql="delete from room_user where room_id=?";
        return jdbcTemplate.update(sql,roomId);
    }

    @Override
    public room queryRoomID(Long host_id, Date date, String stadiumName, String playgroundName) {
        String sql="select * from room where host_id=? and date=? and stadium=? and playground=?";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<room>(room.class),host_id,date,stadiumName,playgroundName);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public int exitRoom(Long roomId, Long userID) {
        String sql="delete from room_user where room_id=? and user_id=?";
        return jdbcTemplate.update(sql,roomId,userID);
    }

    @Override
    public int updatePlayground(Long playgroundId, String bitmaps_today, String bitmaps_tomorrow) {
        String sql="update playground set " +
                "bitmaps_today=?," +
                "bitmaps_tomorrow=? where playground_id=?";
       return jdbcTemplate.update(sql,bitmaps_today,bitmaps_tomorrow,playgroundId);
    }

    @Override
    public int updateUserPK(user user,String studentID) {
        String sql="update match_user set " +
                "pk_basketball=?," +
                "pk_tennis=?," +
                "pk_football=?," +
                "pk_pingpang=?," +
                "pk_badminton=?," +
                "pk_volleyball=?," +
                "pk_baseball=?," +
                "pk_shuttlecock=? " +
                "where student_id=? " ;
        return jdbcTemplate.update(sql,user.getPk_basketball(),user.getPk_tennis(),user.getPk_football(),user.getPk_pingpang(),user.getPk_badminton(),user.getPk_volleyball(),user.getPk_baseball(),user.getShuttlecock(),studentID);
    }


    @Override
    public int updateUserPhoto(String studentID, String photoURL) {
        String sql="update match_user set " +
                "photourl=? where student_id=?";
        return jdbcTemplate.update(sql,photoURL,studentID);
    }
}

